<?php
/**
 * Created by PhpStorm.
 * User: rocky
 * Date: 2019-06-29
 * Time: 00:24
 */

namespace app\common\bevhior;


use think\Db;
use think\db\Query;
use think\facade\Cache;

/*
 * 自动建立索引
 */

class DbAutoIndex
{
    public function run()
    {
        $function = function (Query $query) {
            $query = clone $query;
            $sql = strtolower($query->buildSql());
            //查找like字段
            $likeField = [];
            if (strstr($sql, 'like')) {
                $likeSqlArr = explode(' like', $sql);
                array_pop($likeSqlArr);
                foreach ($likeSqlArr as $value) {
                    preg_match_all('/`(.*)`/U', $value, $arr);
                    if (isset($arr[1])) {
                        $likeField[] = array_pop($arr[1]);
                    }
                }
            }
            $fields = $query->getTableFields();
            $table = $this->getTableName($query->getTable());
            //截取表名后的sql
            $sql = stristr($sql, $table);
            //获取主键
            $pk = $query->getPk();
            $indexField = [];
            //查找本次条件字段,并过滤主键和like字段,字段长度超过191
            foreach ($fields as $field) {
                $fieldType = $query->getFieldsType($table, $field);
                if (preg_match('/\d+/', $fieldType, $arr)) {
                    $fieldTypeLength = $arr[0];
                } else {
                    $fieldTypeLength = 0;
                }
                $res = strripos($sql, "`{$field}`");
                if ($res !== false && $field != $pk && !in_array($field, $likeField) && ($fieldTypeLength > 0 && $fieldTypeLength <= 191)) {
                    $indexField[] = $field;
                }
            }
            //与缓存中比较字段差集
            $cacheIndexFields = unserialize(Cache::get('addindex_' . $table));
            if (!$cacheIndexFields) {
                $cacheIndexFields = [];
            }
            $indexField = array_diff($indexField, $cacheIndexFields);
            if (count($indexField) > 0) {
                //获取表索引字段
                $tableIndex = $query->query('SHOW INDEX FROM ' . $table);
                $tableIndexField = array_column($tableIndex, 'Column_name');
                $tmp_indexField = $indexField;
                $indexField = [];
                //过滤表存在的索引
                foreach ($tmp_indexField as $val) {
                    if (!in_array($val, $tableIndexField)) {
                        $indexField[] = $val;
                    }
                }
                //将要添加索引和表存在的索引合并缓存
                $cacheValue = array_merge($indexField, $tableIndexField);
                Cache::set('addindex_' . $table, serialize($cacheValue));
            }
            //生成添加索引sql
            $exeSql = [];
            foreach ($indexField as $val) {
                $name = $table . '_' . $val;
                $exeSql[] = "alter table {$table} add INDEX `{$name}` (`{$val}`)";
            }
            if (count($exeSql) > 0) {
                $query->batchQuery($exeSql);
            }
        };
        Db::event('before_select', $function);
        Db::event('before_find', $function);
    }

    /**
     * 返回真实数据库表名
     * @param $table 表名 $query->getTable()获得
     * @return int|string|null
     */
    protected function getTableName($table)
    {
        if (is_array($table)) {
            return key($table);
        } else {
            $tables = explode(' ', $table);
            return array_shift($tables);
        }
    }
}